﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SunshineAirlines
{
    public partial class flight_Schedule_Management : Form
    {
        public flight_Schedule_Management()
        {
            InitializeComponent();
        }

        private void label2_Click(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (radioButton1.Checked)
            {//表city中城市名 combox.SelectedItem
                string sql = "select ScheduleId,Date,Time,ca.CityName+'/'+DepartureAirportIATA as 'from',cb.CityName + '/' + ArrivalAirportIATA as 'to',Name,EconomyPrice,FlightNumber,Gate,Status from Schedule,Route,Aircraft,Airport a, Airport b,City ca, City cb where Date >= '" + dateTimePicker1.Value.ToString("yyyy-MM-dd").Trim() + "' and Date<= '" + dateTimePicker2.Value.ToString("yyyy - MM - dd").Trim() + "' and Schedule.AircraftId = Aircraft.AircraftId and Schedule.RouteId = Route.RouteId and ca.CityName='" + comboBox1.SelectedItem.ToString() + "' and cb.CityName='" + comboBox2.SelectedItem.ToString() + "' and ca.CityCode = a.CityCode and a.IATACode = DepartureAirportIATA and cb.CityCode = b.CityCode and b.IATACode = ArrivalAirportIATA order by a.IATACode";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);
            }
            if (radioButton2.Checked)
            {//更改排序
                string sql = "select ScheduleId, Date,Time,ca.CityName+'/'+DepartureAirportIATA as 'from',cb.CityName + '/' + ArrivalAirportIATA as 'to',Name,EconomyPrice,FlightNumber,Gate,Status from Schedule,Route,Aircraft,Airport a, Airport b,City ca, City cb where Date >= '" + dateTimePicker1.Value.ToString("yyyy-MM-dd").Trim() + "' and Date<= '" + dateTimePicker2.Value.ToString("yyyy - MM - dd").Trim() + "' and Schedule.AircraftId = Aircraft.AircraftId and Schedule.RouteId = Route.RouteId and ca.CityName='" + comboBox1.SelectedItem.ToString() + "' and cb.CityName='" + comboBox2.SelectedItem.ToString() + "' and ca.CityCode = a.CityCode and a.IATACode = DepartureAirportIATA and cb.CityCode = b.CityCode and b.IATACode = ArrivalAirportIATA order by ca.CityName";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);
            }
            //强记添加新列
            System.Windows.Forms.DataGridViewLinkColumn a = new DataGridViewLinkColumn();
            a.Name = "detail";
            dataGridView1.Columns.Add(a);
            foreach (DataGridViewRow item in dataGridView1.Rows)
            {
                item.Cells["detail"].Value = "detail";
            }
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {//链接TicketSalesDDetail
            Form f1 = new TicketSalesDDetail(dataGridView1.CurrentRow.Cells["ScheduleId"].Value.ToString());
            f1.ShowDialog();
        }

        private void pictureBox1_Click(object sender, EventArgs e)
        {
            object a = comboBox1.SelectedItem;
            comboBox1.SelectedItem = comboBox2.SelectedItem;
            comboBox2.SelectedItem = a;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            int a = dataGridView1.CurrentCell.RowIndex;
            var b = dataGridView1.CurrentRow.Cells["ScheduleId"].Value.ToString();
            if (dataGridView1.Rows[a].Cells["Status"].Value.ToString() == "Canceled")
            {
                string sql = "update Schedule set Status='Confirmed' where ScheduleId='"+b+"'";
                DBHelper.ExecuteScalar(sql);
            }
            if (dataGridView1.Rows[a].Cells["Status"].Value.ToString() == "Confirmed")
            {
                string sql = "update Schedule set Status='Canceled' where ScheduleId='" + b + "'";
                DBHelper.ExecuteScalar(sql);

            }
            //复制上半renew();
            if (radioButton1.Checked)
            {//表city中城市名 combox.SelectedItem
                string sql = "select ScheduleId,Date,Time,ca.CityName+'/'+DepartureAirportIATA as 'from',cb.CityName + '/' + ArrivalAirportIATA as 'to',Name,EconomyPrice,FlightNumber,Gate,Status from Schedule,Route,Aircraft,Airport a, Airport b,City ca, City cb where Date >= '" + dateTimePicker1.Value.ToString("yyyy-MM-dd").Trim() + "' and Date<= '" + dateTimePicker2.Value.ToString("yyyy - MM - dd").Trim() + "' and Schedule.AircraftId = Aircraft.AircraftId and Schedule.RouteId = Route.RouteId and ca.CityName='" + comboBox1.SelectedItem.ToString() + "' and cb.CityName='" + comboBox2.SelectedItem.ToString() + "' and ca.CityCode = a.CityCode and a.IATACode = DepartureAirportIATA and cb.CityCode = b.CityCode and b.IATACode = ArrivalAirportIATA order by a.IATACode";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);
            }
            if (radioButton2.Checked)
            {//更改排序
                string sql = "select ScheduleId, Date,Time,ca.CityName+'/'+DepartureAirportIATA as 'from',cb.CityName + '/' + ArrivalAirportIATA as 'to',Name,EconomyPrice,FlightNumber,Gate,Status from Schedule,Route,Aircraft,Airport a, Airport b,City ca, City cb where Date >= '" + dateTimePicker1.Value.ToString("yyyy-MM-dd").Trim() + "' and Date<= '" + dateTimePicker2.Value.ToString("yyyy - MM - dd").Trim() + "' and Schedule.AircraftId = Aircraft.AircraftId and Schedule.RouteId = Route.RouteId and ca.CityName='" + comboBox1.SelectedItem.ToString() + "' and cb.CityName='" + comboBox2.SelectedItem.ToString() + "' and ca.CityCode = a.CityCode and a.IATACode = DepartureAirportIATA and cb.CityCode = b.CityCode and b.IATACode = ArrivalAirportIATA order by ca.CityName";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);
            }
        }
    }
}
